package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/**
 * @Auther: MFM
 * @Date: 2021/10/20 - 10 - 20 - 14:48
 * @version: 1.0
 */

public class UserManage {
    private class Users {
        private Long id;
        private String username;
        private String password;
        private String cname;
        private String email;
        private Integer age;
        private Integer money;

        public Long getId() {
            return id;
        }

        public void setId(Long id) {
            this.id = id;
        }

        public String getUsername() {
            return username;
        }

        public void setUsername(String username) {
            this.username = username;
        }

        public String getPassword() {
            return password;
        }

        public void setPassword(String password) {
            this.password = password;
        }

        public String getCname() {
            return cname;
        }

        public void setCname(String cname) {
            this.cname = cname;
        }

        public String getEmail() {
            return email;
        }

        public void setEmail(String email) {
            this.email = email;
        }

        public Integer getAge() {
            return age;
        }

        public void setAge(Integer age) {
            this.age = age;
        }

        public Integer getMoney() {
            return money;
        }

        public void setMoney(Integer money) {
            this.money = money;
        }

        public Users(Long id, String username, String password, String cname, String email, Integer age, Integer money) {
            this.id = id;
            this.username = username;
            this.password = password;
            this.cname = cname;
            this.email = email;
            this.age = age;
            this.money = money;
        }

        @Override
        public String toString() {
            return "Users{" +
                    "id=" + id +
                    ", username='" + username + '\'' +
                    ", password='" + password + '\'' +
                    ", cname='" + cname + '\'' +
                    ", email='" + email + '\'' +
                    ", age=" + age +
                    ", money=" + money +
                    '}';
        }
    }

    private class Page {
        private int pageSize = 10;
        private int currentPage = 1;
        private int totalPage = 0;//需要计算
        private int totalSize = 0;
        private List<Users> list = new ArrayList<>();

        public int getPageSize() {
            return pageSize;
        }

        public void setPageSize(int pageSize) {
            this.pageSize = pageSize;
        }

        public int getCurrentPage() {
            return currentPage;
        }

        public void setCurrentPage(int currentPage) {
            if (currentPage < 1) {
                this.currentPage = 1;
            } else if (currentPage > totalPage) {
                this.currentPage = totalPage;
            } else {
                this.currentPage = currentPage;
            }

        }

        public int getTotalPage() {
            return totalPage;
        }

        public void setTotalPage(int totalPage) {

            this.totalPage = totalPage;
        }

        public int getTotalSize() {
            return totalSize;
        }

        public void setTotalSize(int totalSize) {
            this.totalSize = totalSize;
            //计算总页数
            if (totalSize % pageSize == 0) {
                this.totalPage = totalSize / pageSize;
            } else {
                this.totalPage = totalSize / pageSize + 1;
            }
        }

        public List<Users> getList() {
            return list;
        }

        public void setList(List<Users> list) {
            this.list = list;
        }
    }

    private Users admin = null;
    private Scanner scan = new Scanner(System.in);
    private Page page = new Page();

    public static void main(String[] args) throws Exception {
        new UserManage().run();
    }

    public void run() throws Exception {
        Connection conn = getConn();
        this.login(conn);
        if (admin != null) {
            System.out.println("欢迎" + admin.cname + "登陆系统");
            boolean goon = true;
            while (goon) {
                System.out.println("请选择操作:1、列表查询 2、新增 3、修改 4、删除 5、退出系统");
                String operate = scan.nextLine();
                switch (operate) {
                    case "1":
                        queryList(conn);
                        break;
                    case "2":
                        addUsers(conn);
                        break;
                    case "3":
                        updateUser(conn);
                        break;
                    case "4":
                        deleteUser(conn);
                        break;
                    case "5":
                        goon = false;
                        break;
                    default:
                        System.out.println("不支持此操作，请重新选择");
                        break;
                }

            }
            System.out.println("谢谢使用!");
        }
    }

    private void deleteUser(Connection conn) throws Exception {
        this.queryList(conn);
        System.out.println("请输入需要删除的记录id");
        Long id = Long.parseLong(scan.nextLine());
        System.out.println("确认删除该条记录 Y/N");
        String operate = scan.nextLine();
        if ("Y".equalsIgnoreCase(operate)) {
            String sql = "delete from users where id=?";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setLong(1, id);
            boolean set = statement.execute();
            if (!set){
                System.out.println("删除成功！");
            }else {
                System.out.println("删除失败，请联系管理人员！");
            }
        }

    }

    private void updateUser(Connection conn) throws Exception {
        this.queryList(conn);
        System.out.println("请输入要修改的记录！");
        int size = Integer.parseInt(scan.nextLine());
        if (size < 0 || size > page.getPageSize()) {
            System.out.println("要修改的记录不存在");
        } else {
            Users user = page.getList().get(size - 1);
            System.out.println("用户昵称：" + user.getCname() + "，是否需要修改？Y/N");
            String operate = scan.nextLine();
            if ("Y".equalsIgnoreCase(operate)) {
                System.out.println("请输入新昵称：");
                String cname = scan.nextLine();
                user.setCname(cname);
            }
            System.out.println("用户邮箱：" + user.getEmail() + "，是否需要修改？Y/N");
            operate = scan.nextLine();
            if ("Y".equalsIgnoreCase(operate)) {
                System.out.println("请输入新邮箱：");
                String email = scan.nextLine();
                user.setEmail(email);
            }
            String sql = "update users set cname=?,email=? where id=?";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, user.getCname());
            statement.setString(2, user.getEmail());
            statement.setLong(3, user.getId());
            int row = statement.executeUpdate();
            if (row > 0) {
                System.out.println("数据更新成功");
            } else {
                System.out.println("操作失败，请联系管理人员!");
            }
        }
    }

    private void queryList(Connection conn) throws Exception {
        String operate = "Y";
        while ("Y".equalsIgnoreCase(operate)) {
            String sql = "select count(id) from users";
            PreparedStatement statement = conn.prepareStatement(sql);
            ResultSet set = statement.executeQuery();
            set.next();
            int totalSize = set.getInt(1);
            page.setTotalSize(totalSize);
            if (page.getCurrentPage() != 1) {
                System.out.println("请输入要查看的页数");
                int currentPage = Integer.parseInt(scan.nextLine());
                page.setCurrentPage(currentPage);
            }
            int startIndex = (page.getCurrentPage() - 1) * page.getPageSize();
            String sql1 = "select * from users limit ?,?";
            statement = conn.prepareStatement(sql1);
            statement.setInt(1, startIndex);
            statement.setInt(2, page.getPageSize());
            set = statement.executeQuery();
            List<Users> list = new ArrayList<>();
            while (set.next()) {
                Long id = set.getLong("id");
                String username = set.getString("username");
                String password = set.getString("password");
                String cname = set.getString("cname");
                String email = set.getString("email");
                Integer age = set.getInt("age");
                Integer money = set.getInt("money");
                Users user = new Users(id, username, password, cname, email, age, money);
                list.add(user);
            }
            page.setList(list);
            for (Users users : list) {
                System.out.println(users);
            }
            System.out.println("当前页数：" + page.getCurrentPage() + ",共页" + page.getTotalPage() + "页");
            System.out.println("是否继续查看其它页数？Y/N");
            operate = scan.nextLine();
            page.setCurrentPage(page.getCurrentPage() + 1);
        }
    }

    private void addUsers(Connection conn) throws Exception {
        System.out.println("请输入用户名");
        String username = scan.nextLine();
        System.out.println("请输入密码");
        String password = scan.nextLine();
        System.out.println("请输入昵称");
        String cname = scan.nextLine();
        System.out.println("请输入邮箱");
        String email = scan.nextLine();
        System.out.println("请输入年龄");
        Integer age = Integer.parseInt(scan.nextLine());
        System.out.println("请输入金额");
        Integer money = Integer.parseInt(scan.nextLine());
        //创建能够执行sql语句的statement
        String sql = "insert into users(id,username,password,cname,email,age,money) value (null,?,?,?,?,?,?)";
        PreparedStatement statement = conn.prepareStatement(sql);
        statement.setString(1, username);
        statement.setString(2, password);
        statement.setString(3, cname);
        statement.setString(4, email);
        statement.setInt(5, age);
        statement.setInt(6, money);
        int row = statement.executeUpdate();
        if (row > 0) {
            System.out.println("数据添加完毕！");
        } else {
            System.out.println("操作异常，请联系管理人员!");
        }
    }


    private void login(Connection conn) throws Exception {
        String sql = "select * from users where username=?and password=?";
        PreparedStatement statement = conn.prepareStatement(sql);
        for (int i = 0; i < 3; i++) {
            System.out.println("请输入用户名");
            String username = scan.nextLine();
            System.out.println("请输入密码");
            String password = scan.nextLine();
            statement.setString(1, username);
            statement.setString(2, password);
            ResultSet set = statement.executeQuery();
            if (set.next()) {
                Long id = set.getLong("id");
                String cname = set.getString("cname");
                String email = set.getString("email");
                Integer age = set.getInt("age");
                Integer money = set.getInt("money");
                admin = new Users(id, username, password, cname, email, age, money);
                break;
            } else {
                System.out.println("用户名或密码不正确!");
            }
        }
    }

    public static Connection getConn() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8&useSSL=false&useUnicode=true&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        Connection conn = DriverManager.getConnection(url, "root", "52zjx1314");
        return conn;
    }
}
